package DBLayer;
import ModelLayer.Order;
import java.sql.*;
import java.util.ArrayList;

public class DBOrder {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBOrder() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Order getOrderByID(int id) {
        return selectSingle("SELECT `id_order`, `id_carrier`, `id_lang`, `id_customer`, `id_cart`, `id_currency`, `id_address_delivery`, `id_address_invoice`, `secure_key`, `payment`, `module`, `recyclable`, `gift`, `gift_message`, `shipping_number`, `total_discounts`, `total_paid`, `total_paid_real`, `total_products`, `total_shipping`, `total_wrapping`, `invoice_number`, `delivery_number`, UNIX_TIMESTAMP(`invoice_date`) AS `invoice_date`, UNIX_TIMESTAMP(`delivery_date`) AS `delivery_date`, `valid`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd` FROM `ps_orders` WHERE `id_order` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Order> getOrderList() {
        return selectMultiple("SELECT `id_order`, `id_carrier`, `id_lang`, `id_customer`, `id_cart`, `id_currency`, `id_address_delivery`, `id_address_invoice`, `secure_key`, `payment`, `module`, `recyclable`, `gift`, `gift_message`, `shipping_number`, `total_discounts`, `total_paid`, `total_paid_real`, `total_products`, `total_shipping`, `total_wrapping`, `invoice_number`, `delivery_number`, UNIX_TIMESTAMP(`invoice_date`) AS `invoice_date`, UNIX_TIMESTAMP(`delivery_date`) AS `delivery_date`, `valid`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd` FROM `ps_orders` ORDER BY `id_order` ASC;");
    }

    public ArrayList<Integer> getOrderIDsByDiscountID(int id) {
        return selectIDs("SELECT `id_order` FROM `ps_order_discount` WHERE `id_discount` = " + id + " ORDER BY `id_order` ASC;");
    }

    public int getNextInvoiceNumber() {
        Statement stmt = null;
        ResultSet result = null;
        int nextInvoiceNumber = 0;
        
        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery("SELECT MAX(`invoice_number`) AS `invoice_number` FROM `ps_orders` LIMIT 0, 1;");

            if (result.next()) {
                nextInvoiceNumber = (result.getInt("invoice_number") + 1);
            }
            System.out.println("Fetching Order Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }

        return nextInvoiceNumber;
    }

    private Order selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Order order = new Order();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                order = buildOrder(result);
            }
            System.out.println("Fetching Order Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return order;
    }

    private ArrayList<Order> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Order> orders = new ArrayList<Order>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Order order = new Order();
                order = buildOrder(result);
                orders.add(order);
            }
            System.out.println("Fetching Order List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orders;
    }

   private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_order"));
            }
            System.out.println("Fetching Order ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

    private Order buildOrder(ResultSet result) {
        DBDiscount dbDiscount = new DBDiscount();
        Order order = new Order();
        try {
            order.setIDOrder(result.getInt("id_order"));
            order.setIDCarrier(result.getInt("id_carrier"));
            order.setIDLang(result.getInt("id_lang"));
            order.setIDCustomer(result.getInt("id_customer"));
            order.setIDCart(result.getInt("id_cart"));
            order.setIDCurrency(result.getInt("id_currency"));
            order.setIDAddressDelivery(result.getInt("id_address_delivery"));
            order.setIDAddressInvoice(result.getInt("id_address_invoice"));
            order.setSecureKey(result.getString("secure_key"));
            order.setPayment(result.getString("payment"));
            order.setModule(result.getString("module"));
            if (result.getInt("recyclable") == 1) {
                order.setRecyclable(true);
            } else {
                order.setRecyclable(false);
            }
            if (result.getInt("gift") == 1) {
                order.setGift(true);
            } else {
                order.setGift(false);
            }
            order.setGiftMessage(result.getString("gift_message"));
            order.setShippingNumber(result.getString("shipping_number"));
            order.setTotalDiscounts(result.getFloat("total_discounts"));
            order.setTotalPaid(result.getFloat("total_paid"));
            order.setTotalPaidReal(result.getFloat("total_paid_real"));
            order.setTotalProducts(result.getFloat("total_products"));
            order.setTotalShipping(result.getFloat("total_shipping"));
            order.setTotalWrapping(result.getFloat("total_wrapping"));
            order.setInvoiceNumber(result.getInt("invoice_number"));
            order.setDeliveryNumber(result.getInt("delivery_number"));
            order.setInvoiceDate(result.getInt("invoice_date"));
            order.setDeliveryDate(result.getInt("delivery_date"));
            if (result.getInt("valid") == 1) {
                order.setValid(true);
            } else {
                order.setValid(false);
            }
            order.setDateAdd(result.getInt("date_add"));
            order.setDateUpd(result.getInt("date_upd"));
            order.setDiscountIDs(dbDiscount.getDiscountIDsByOrderID(result.getInt("id_order")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return order;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addOrder(Order order) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_orders` (`id_carrier`, `id_lang`, `id_customer`, `id_cart`, `id_currency`, `id_address_delivery`, `id_address_invoice`, `secure_key`, `payment`, `module`, `recyclable`, `gift`, `gift_message`, `shipping_number`, `total_discounts`, `total_paid`, `total_paid_real`, `total_products`, `total_shipping`, `total_wrapping`, `invoice_number`, `delivery_number`, `invoice_date`, `delivery_date`, `valid`, `date_add`, `date_upd`) "
                + "VALUES (" + order.getIDCarrier() + ", " + order.getIDLang() + ", " + order.getIDCustomer() + ", " + order.getIDCart() + ", " + order.getIDCurrency() + ", " + order.getIDAddressDelivery() + ", " + order.getIDAddressInvoice() + ", MD5(RAND()), '" + order.getPayment() + "', '" + order.getModule() + "', ";
        if (order.getRecyclable() == true)  { query += "1, "; }
        else                                { query += "0, "; }
        if (order.getGift() == true)        { query += "1, "; }
        else                                { query += "0, "; }
        query += "'" + order.getGiftMessage() + "', '" + order.getShippingNumber() + "', " + order.getTotalDiscounts() + ", " + order.getTotalPaid() +  ", " + order.getTotalPaidReal() + ", " + order.getTotalProducts() + ", " + order.getTotalShipping() + ", " + order.getTotalWrapping() + ", " + order.getInvoiceNumber() + ", " + order.getDeliveryNumber() + ", NOW(), " + order.getDeliveryDate() + ", ";
        if (order.getValid() == true)       { query += "1, "; }
        else                                { query += "0, "; }
        query += "NOW(), NOW());";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Order ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateOrder(Order order, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_orders` SET "
                + "`id_order`               = "  + order.getIDOrder() + ", "
                + "`id_carrier`             = "  + order.getIDCarrier() + ", "
                + "`id_lang`                = "  + order.getIDLang() + ", "
                + "`id_customer`            = "  + order.getIDCustomer() + ", "
                + "`id_cart`                = "  + order.getIDCart() + ", "
                + "`id_currency`            = "  + order.getIDCurrency() + ", "
                + "`id_address_delivery`    = "  + order.getIDAddressDelivery() + ", "
                + "`id_address_invoice`     = "  + order.getIDAddressInvoice() + ", "
                + "`secure_key`             = '" + order.getSecureKey() + "', "
                + "`payment`                = '" + order.getPayment() + "', "
                + "`module`                 = '" + order.getModule() + "', ";
        if (order.getRecyclable() == true)  { query += "`recyclable` = 1, "; }
        else                                { query += "`recyclable` = 0, "; }
        if (order.getGift() == true)        { query += "`gift` = 1, "; }
        else                                { query += "`gift` = 0, "; }
        query +=  "`gift_message`           = '" + order.getGiftMessage() + "', "
                + "`shipping_number`        = '" + order.getShippingNumber() + "', "
                + "`total_discounts`        = "  + order.getTotalDiscounts() + ", "
                + "`total_paid`             = "  + order.getTotalPaid() + ", "
                + "`total_paid_real`        = "  + order.getTotalPaidReal() + ", "
                + "`total_products`         = "  + order.getTotalProducts() + ", "
                + "`total_shipping`         = "  + order.getTotalShipping() + ", "
                + "`total_wrapping`         = "  + order.getTotalWrapping() + ", "
                + "`invoice_number`         = "  + order.getInvoiceNumber() + ", "
                + "`delivery_number`        = "  + order.getDeliveryNumber() + ", "
                + "`invoice_date`           = FROM_UNIXTIME("  + order.getInvoiceDate() + "), "
                + "`delivery_date`          = FROM_UNIXTIME("  + order.getDeliveryDate() + "), ";
        if (order.getValid() == true)       { query += "`valid` = 1, "; }
        else                                { query += "`valid` = 0, "; }
        query +=  "`date_add`               = FROM_UNIXTIME("  + order.getDateAdd() + "), "
                + "`date_upd`               = FROM_UNIXTIME("  + order.getDateUpd() + ") "
                + "WHERE `id_order` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = order.getIDOrder();
            System.out.println("Updated Order ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteOrder(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_orders` WHERE `id_order` = " + id + " LIMIT 1;");
            System.out.println("Deleted Order ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
